﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CommonLibrary
{
    /// <summary>
    /// 数据库操作基类
    /// 实现对Sql数据库的各种操作
    /// 创建时间：2006-8-3
    /// </summary>
    public class SqlDataBase
    {
        public SqlDataBase(string connstr)
        {
            this._sqlConnectionString = ConfigurationManager.ConnectionStrings[connstr].ConnectionString;
        }
        //获取Web.Config数据库连接字符串
        private readonly string _sqlConnectionString; 
        //ConfigurationManager.ConnectionStrings["db_CSConnectionString1"].ConnectionString;

        private SqlConnection cn;		//创建SQL连接
        private SqlDataAdapter sda;		//创建SQL数据适配器
        private SqlDataReader sdr;		//创建SQL数据读取器
        private SqlCommand cmd;			//创建SQL命令对象
        private SqlParameter param;     //创建SQL参数
        private DataSet ds;				//创建数据集
        private DataView dv;			//创建视图       

        #region 暂时未用
        /// <summary>
        /// 打开数据库连接
        /// </summary>
        public void Open()
        {
            #region
            cn = new SqlConnection(_sqlConnectionString);
            cn.Open();
            #endregion
        }


        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void Close()
        {
            #region
            if (cn != null)
            {
                cn.Close();
                cn.Dispose();
            }
            #endregion
        }


        /// <summary>
        /// 返回DataSet数据集
        /// </summary>
        /// <param name="strSql">SQL语句</param>
        public DataSet GetDs(string strSql)
        {
            #region
            Open();
            sda = new SqlDataAdapter(strSql, cn);
            ds = new DataSet();
            sda.Fill(ds);
            Close();
            return ds;
            #endregion
        }

        /// <summary>
        /// 添加DataSet表
        /// </summary>
        /// <param name="ds">DataSet对象</param>
        /// <param name="strSql">Sql语句</param>
        /// <param name="strTableName">表名</param>
        public void GetDs(DataSet ds, string strSql, string strTableName)
        {
            #region
            Open();
            sda = new SqlDataAdapter(strSql, cn);
            sda.Fill(ds, strTableName);
            Close();
            #endregion
        }


        /// <summary>
        /// 返回DataView数据视图
        /// </summary>
        /// <param name="strSql">Sql语句</param>
        public DataView GetDv(string strSql)
        {
            #region
            dv = GetDs(strSql).Tables[0].DefaultView;
            return dv;
            #endregion
        }


        /// <summary>
        /// 获得DataTable对象
        /// </summary>
        /// <param name="strSql">SQL语句</param>
        /// <returns></returns>
        public DataTable GetTable(string strSql)
        {
            #region
            return GetDs(strSql).Tables[0];
            #endregion
        }


        /// <summary>
        /// 获得SqlDataReader对象 使用完须关闭DataReader,关闭数据库连接
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <returns></returns>
        public SqlDataReader GetDataReader(string strSql)
        {
            #region
            Open();
            cmd = new SqlCommand(strSql, cn);
            sdr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
            return sdr;
            #endregion
        }



        /// <summary>
        /// 执行Sql语句
        /// </summary>
        /// <param name="strSql"></param>
        public void RunSql(string strSql)
        {
            #region
            Open();
            cmd = new SqlCommand(strSql, cn);
            cmd.ExecuteNonQuery();
            Close();
            #endregion
        }



        /// <summary>
        /// 执行SQL语句，并返回第一行第一列结果
        /// </summary>
        /// <param name="strSql">SQL语句</param>
        /// <returns></returns>
        public string RunSqlReturn(string strSql)
        {
            #region
            string strReturn = "";
            Open();
            try
            {
                cmd = new SqlCommand(strSql, cn);
                strReturn = cmd.ExecuteScalar().ToString();
            }
            catch { }
            Close();
            return strReturn;
            #endregion
        }


        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procName">存储过程的名称</param>
        /// <returns>返回存储过程返回值</returns>
        public int RunProcNonQuery(string procName)
        {
            #region
            cmd = CreateCommand(procName, null);
            cmd.ExecuteNonQuery();
            return (int)cmd.Parameters["ReturnValue"].Value;
            #endregion
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="prams">存储过程所需参数</param>
        /// <returns>返回存储过程返回值</returns>
        public int RunProcNonQuery(string procName, SqlParameter[] prams)
        {
            #region
            //Log.Debug(this.GetType().ToString(), "创建订单SQL未执行");
            cmd = CreateCommand(procName, prams);
            cmd.ExecuteNonQuery();
            //Log.Debug(this.GetType().ToString(), "创建订单SQL已执行");
            return (int)cmd.Parameters["Return_Value"].Value;
            //return 1;
            #endregion
        }

        /// <summary>
        /// 执行存储过程返回DataReader对象
        /// </summary>
        /// <param name="procName">Sql语句</param>
        /// <param name="dataReader">DataReader对象</param>
        private SqlDataReader RunProc(string procName)
        {
            #region
            cmd = CreateCommand(procName, null);
            SqlDataReader dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
            return dataReader;
            #endregion
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procName">存储过程的名称</param>
        /// <param name="prams">存储过程所需参数</param>
        /// <param name="dataReader">DataReader对象</param>
        private SqlDataReader RunProc(string procName, SqlParameter[] prams)
        {
            #region
            cmd = CreateCommand(procName, prams);
            SqlDataReader dataReader = cmd.ExecuteReader();//System.Data.CommandBehavior.CloseConnection
            return dataReader;

            #endregion
        }

        public DataSet ExecuteProc(string procName, SqlParameter[] prams, out int 记录数)
        {
            SqlDataReader dr = RunProc(procName, prams);
            var ds = ConvertDataReaderToDataSet(dr);
            记录数 = int.Parse(cmd.Parameters["Return_Value"].Value.ToString());
            return ds;
        }
        public DataSet ExecuteProc(string procName, SqlParameter[] prams)
        {
            SqlDataReader dr = RunProc(procName, prams);
            var ds = ConvertDataReaderToDataSet(dr);
            return ds;
        }

        /// <summary>
        /// 创建一个SqlCommand对象以此来执行存储过程
        /// </summary>
        /// <param name="procName">存储过程的名称</param>
        /// <param name="prams">存储过程所需参数</param>
        /// <returns>返回SqlCommand对象</returns>
        private SqlCommand CreateCommand(string procName, SqlParameter[] prams)
        {
            #region
            // 确认打开连接
            Open();
            cmd = new SqlCommand(procName, cn);
            cmd.CommandType = CommandType.StoredProcedure;

            // 依次把参数传入存储过程
            if (prams != null)
            {
                foreach (SqlParameter parameter in prams)
                    cmd.Parameters.Add(parameter);
            }

            //  加入返回参数
            //cmd.Parameters.Add(
            //    new SqlParameter("Return_Value", SqlDbType.Int, 4,
            //    ParameterDirection.ReturnValue, false, 0, 0,
            //    string.Empty, DataRowVersion.Default, null));
            var RETURN_VALUE = new SqlParameter("Return_Value", SqlDbType.Int)
            {
                Direction = ParameterDirection.ReturnValue
            };
            cmd.Parameters.Add(RETURN_VALUE);
            //SqlParameter parOutput = cmd.Parameters.Add("@recor", SqlDbType.Int, 4);　　//定义输出参数
            //parOutput.Direction = ParameterDirection.Output;　　//参数类型为Output
            //
            return cmd;
            #endregion
        }


        /// <summary>
        /// 传入输入参数
        /// </summary>
        /// <param name="ParamName">存储过程名称</param>
        /// <param name="DbType">参数类型</param></param>
        /// <param name="Size">参数大小</param>
        /// <param name="Value">参数值</param>
        /// <returns>新的 parameter 对象</returns>
        public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
        {
            #region
            return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);

            #endregion
        }

        /// <summary>
        /// 传入返回值参数
        /// </summary>
        /// <param name="ParamName">存储过程名称</param>
        /// <param name="DbType">参数类型</param>
        /// <param name="Size">参数大小</param>
        /// <returns>新的 parameter 对象</returns>
        public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
        {
            #region
            return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
            #endregion
        }


        /// <summary>
        /// 生成存储过程参数
        /// </summary>
        /// <param name="ParamName">存储过程名称</param>
        /// <param name="DbType">参数类型</param>
        /// <param name="Size">参数大小</param>
        /// <param name="Direction">参数方向</param>
        /// <param name="Value">参数值</param>
        /// <returns>新的 parameter 对象</returns>
        public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
        {
            #region

            if (Size > 0)
                param = new SqlParameter(ParamName, DbType, Size);
            else
                param = new SqlParameter(ParamName, DbType);

            param.Direction = Direction;
            if (!(Direction == ParameterDirection.Output && Value == null))
                param.Value = Value;

            return param;
            #endregion
        }
        #endregion

        /// <summary>
        /// 用来执行带参数和不带参数仅返回所影响的行数的sql语句，如：insert，delete，update
        /// </summary>
        /// <param name="sql">所要执行的sql语句</param>
        /// <param name="pams">sql语句中所带的参数</param>
        /// <returns>返回所影响的行数</returns>
        public int ExecuteNonQuery(string sql, params SqlParameter[] pams)
        {

            Open();
            cmd = new SqlCommand(sql, cn);
            if (pams != null)
            {
                cmd.Parameters.AddRange(pams);
            }
            var tmp = cmd.ExecuteNonQuery();
            Close();
            return tmp;
        }
        /// <summary>
        /// 返回一个DataTable，可以执行select语句
        /// </summary>
        /// <param name="sql">所要执行的sql语句</param>
        /// <param name="pams">sql语句中所带的参数</param>
        /// <returns>返回一个DataTable</returns>
        public DataTable ExecuteDataTable(string sql, params SqlParameter[] pams)
        {
            //cmd = CreateCommand(sql, pams);
            //SqlDataReader dataReader;
            //dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

            Open();
            sda = new SqlDataAdapter(sql, cn);
            //sda.SelectCommand = CreateCommand(sql, pams);
            if (pams != null)
            {
                //adapter.SelectCommand.Parameters.Clear();
                sda.SelectCommand.Parameters.AddRange(pams);
            }
            DataTable dt = new DataTable();
            sda.Fill(dt);
            Close();
            return dt;
            //using (SqlDataAdapter adapter = new SqlDataAdapter(sql, _sqlConnectionString))
            //{
            //    if (pams != null)
            //    {
            //        //adapter.SelectCommand.Parameters.Clear();
            //        sda.SelectCommand.Parameters.AddRange(pams);
            //    }
            //    DataTable dt = new DataTable();
            //    sda.Fill(dt);
            //    return dt;
            //}
        }
        /// <summary>
        /// 事务
        /// </summary>
        /// <param name="SQLStringList"></param>
        public void ExecuteSqlTran(Dictionary<string, SqlParameter[]> SQLStringList)
        {
            Open();
            SqlTransaction trans = cn.BeginTransaction();
            SqlCommand comm = new SqlCommand();
            try
            {
                //循环
                foreach (var myDE in SQLStringList)
                {
                    string cmdText = myDE.Key.ToString();
                    if (myDE.Value != null)
                    {
                        SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
                        comm.Parameters.AddRange(cmdParms);
                    }
                    comm.Connection = cn;
                    comm.CommandText = cmdText;
                    comm.Transaction = trans;
                    int val = comm.ExecuteNonQuery();
                    comm.Parameters.Clear();
                }
                trans.Commit();
            }
            catch (Exception e)
            {
                trans.Rollback();
                throw new Exception(e.Message);
            }
            finally
            {
                Close();
            }
        }

        public void TableValuedToDB(string strSql, string parm, DataTable dt, string TypeName)
        {

            try
            {
                Open();
                cmd = new SqlCommand(strSql, cn);
                SqlParameter catParam = cmd.Parameters.AddWithValue(parm, dt);
                catParam.SqlDbType = SqlDbType.Structured;
                //表值参数的名字叫BulkUdt，在上面的建立测试环境的SQL中有。  
                catParam.TypeName = TypeName;
                if (dt != null && dt.Rows.Count != 0)
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                Close();
            }
        }

        private DataSet ConvertDataReaderToDataSet(SqlDataReader reader)
        {
            DataSet dataSet = new DataSet();
            do
            {
                // Create new data table
                DataTable schemaTable = reader.GetSchemaTable();
                DataTable dataTable = new DataTable();
                if (schemaTable != null)
                {
                    // A query returning records was executed 
                    for (int i = 0; i < schemaTable.Rows.Count; i++)
                    {
                        DataRow dataRow = schemaTable.Rows[i];
                        // Create a column name that is unique in the data table 
                        string columnName = (string)dataRow["ColumnName"]; //+ " // Add the column definition to the data table 
                        DataColumn column = new DataColumn(columnName, (Type)dataRow["DataType"]);
                        dataTable.Columns.Add(column);
                    }
                    dataSet.Tables.Add(dataTable);
                    // Fill the data table we just created
                    while (reader.Read())
                    {
                        DataRow dataRow = dataTable.NewRow();
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            dataRow[i] = reader.GetValue(i);
                        }
                        dataTable.Rows.Add(dataRow);
                    }
                }
                else
                {
                    // No records were returned
                    DataColumn column = new DataColumn("RowsAffected");
                    dataTable.Columns.Add(column);
                    dataSet.Tables.Add(dataTable);
                    DataRow dataRow = dataTable.NewRow();
                    dataRow[0] = reader.RecordsAffected;
                    dataTable.Rows.Add(dataRow);
                }
            }
            while (reader.NextResult());
            return dataSet;
        }

    }
}
